﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using DAO;
using System.Runtime.InteropServices;

namespace TAM_SPORT
{

    public class ExcelManager
    {
        private int pid = -1;
        private void releaseObject(Object ob)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ob);
                ob = null;
            }
            catch (Exception ex)
            {
                ob = null;
                Console.WriteLine(ex.ToString());
            }
            GC.Collect();
        }

        [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern int GetWindowThreadProcessId(HandleRef handle, out int processId);

        private void KillProcess(int pid, string processName)
        {
            // to kill current process of excel
            System.Diagnostics.Process[] AllProcesses = System.Diagnostics.Process.GetProcessesByName(processName);
            foreach (System.Diagnostics.Process process in AllProcesses)
            {
                if (process.Id == pid)
                {
                    process.Kill();
                }
            }
            AllProcesses = null;
        }

        public void ExportDebitCustomer(string name, int status, DateTime fromDate, DateTime toDate, string outPutFile)
        {
            Excel.Application application = new Excel.Application();
            Excel.Workbook workbook = application.Workbooks.Open(System.AppDomain.CurrentDomain.BaseDirectory + @"Resources\Excels\DebitCustomers.xlsx");
            HandleRef hwnd = new HandleRef(application, (IntPtr)application.Hwnd);
            GetWindowThreadProcessId(hwnd, out pid);

            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
            Excel.Range oRng;
            Excel.Range oRng1;

            int indexX = 6;
            int stt = 1;
            SalesDao dao = new SalesDao();
            DataTable table = dao.getDebitCustomer(name, status, fromDate, toDate);
            try
            {
                worksheet.Cells[4, 1] = "Ngày in: " + DateTime.Now.ToString("dd/MM/yyyy");
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        worksheet.Cells[indexX, 1] = stt;
                        worksheet.Cells[indexX, 2] = row["CUSTOMER_ID"].ToString();
                        worksheet.Cells[indexX, 3] = row["NAME"].ToString();
                        worksheet.Cells[indexX, 4] = Convert.ToDouble(row["DEBIT_MONEY"]).ToString("#,###.##");
                        if (stt != table.Rows.Count)
                        {
                            oRng = (Excel.Range)worksheet.Rows.Item[indexX + 1];
                            oRng.Select();
                            oRng.Copy();
                            oRng1 = (Excel.Range)worksheet.Rows.Item[indexX + 2];
                            oRng1.Insert();
                            oRng1.PasteSpecial(Excel.XlPasteType.xlPasteFormats);
                            indexX += 1;
                            stt += 1;
                        }
                    }
                }
                workbook.SaveAs(outPutFile);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                application.DisplayAlerts = false;
                workbook.Close();
                application.DisplayAlerts = true;
                application.Quit();
                releaseObject(application);
                releaseObject(workbook);
                releaseObject(worksheet);
                KillProcess(pid, "EXCEL");
            }
        }

        public void ExportCustomers(string name, string outPutFile)
        {
            Excel.Application application = new Excel.Application();
            Excel.Workbook workbook = application.Workbooks.Open(System.AppDomain.CurrentDomain.BaseDirectory + @"Resources\Excels\Customers.xlsx");
            HandleRef hwnd = new HandleRef(application, (IntPtr)application.Hwnd);
            GetWindowThreadProcessId(hwnd, out pid);

            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets[1];
            Excel.Range oRng;
            Excel.Range oRng1;

            int indexX = 6;
            int stt = 1;
            PurchaseDao dao = new PurchaseDao();
            DataTable table = dao.getCustommer(name, "T");
            try
            {
                worksheet.Cells[4, 1] = "Ngày in: " + DateTime.Now.ToString("dd/MM/yyyy");
                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        worksheet.Cells[indexX, 1] = stt;
                        worksheet.Cells[indexX, 2] = row["ID"].ToString(); 
                        worksheet.Cells[indexX, 3] = row["NAME"].ToString();
                        worksheet.Cells[indexX, 4] = (row["PURCHASE_DATE"].GetType().FullName == "System.DBNull") ? "" : DateTime.Parse(row["PURCHASE_DATE"].ToString()).ToString("dd/MM/yyyy");
                        worksheet.Cells[indexX, 5] = (row["EXPIRATION_DATE"].GetType().FullName == "System.DBNull") ? "" : DateTime.Parse(row["EXPIRATION_DATE"].ToString()).ToString("dd/MM/yyyy");
                        worksheet.Cells[indexX, 6] = row["NOTE"].ToString();
                        if (stt != table.Rows.Count)
                        {
                            oRng = (Excel.Range)worksheet.Rows.Item[indexX + 1];
                            oRng.Select();
                            oRng.Copy();
                            oRng1 = (Excel.Range)worksheet.Rows.Item[indexX + 2];
                            oRng1.Insert();
                            oRng1.PasteSpecial(Excel.XlPasteType.xlPasteFormats);
                            indexX += 1;
                            stt += 1;
                        }
                    }
                }
                workbook.SaveAs(outPutFile);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                application.DisplayAlerts = false;
                workbook.Close();
                application.DisplayAlerts = true;
                application.Quit();
                releaseObject(application);
                releaseObject(workbook);
                releaseObject(worksheet);
                KillProcess(pid, "EXCEL");
            }
        }
    }
}
